<?php
/*
*   phpMySQLi_jacket 0.1
*/

// minimum options, see source for more
$opts = array(
        'host' => 'localhost',
    	'port' => 6033,
        'username' => 'root',
        'password' => 'root',
        'database' => 'test',
);
$db = new phpMySQLi($opts);

// simple select; query is not prepared, thus cachable by MySQL
$result = $db->select('SELECT * FROM t_test WHERE 1 =1');
while ($row = $result->fetch_assoc()) {
	echo $row['alias'].' '.$row['name'].'<br/>';
}
echo '<hr/>';



/*
phpMySQLi_jacket is a wrapper to interface PHP with MySQL using the mysqli extension in PHP

Write parametrized queries easily (prepare, bind, execute)
Call non parametrized queries (for MySQL cache)
Reduce the amount of code you have to write
Analyze your queries
And more..

Developed against PHP 5.3.2, MySQL 5.0 and 5.1, Apache 2

Example usage of phpMySQLi_jacket

include 'phpMySQLi_jacket.class.php';

// use your own class, if wanna
class my_db extends phpMySQLi_jacket {
}

// minimum options, see source for more
$opts = array(
	'host' => '127.0.0.1',
	'username' => 'accounting',
	'password' => '1234',
	'database' => 'sales',
);
$db = new my_db($opts);

// simple select; query is not prepared, thus cachable by MySQL
$result = $db->select('SELECT alias, name FROM users WHERE alias = 'bob'');
while ($row = $result->fetch_assoc()) {
	echo $row['alias'].' '.$row['name'].'<br/>';
}
echo '<hr/>';

// select with parameterization ie auto escaping of user input
// ? are placeholders for the values
// ss is the data type of the column; s = string, i = integer, see source for more
// array() holds the actual values
$result = $db->select('SELECT alias, name FROM users WHERE alias IN (?,?)', 'ss', array('bob','donna'));
while ($row = $result->fetch_object()) {
	echo $row->alias.' '.$row->name.'<br/>';
}
echo '<hr/>';

// call a stored proc with parameterization
// results returned as an array, optionally
$sql  = "CALL qtrSales(?)";
$rows = $db->proc($sql, 'i', array(4));
foreach ($rows as $row) {
    echo $row['sales'].'<br/>';
}
echo '<hr/>';

// update query; could have also used the method update() which is an alias of execute()
$nbr_affected_rows = $db->execute("UPDATE users SET name = ? WHERE alias = 'bob'", 's', array('Bobby'));
echo $nbr_affected_rows;
echo '<hr/>';

// insert query; could have also used the method insert() which is an alias of execute()
$insert_id = $db->execute("INSERT INTO users (name, alias) VALUES (?, ?)", 'ss', array('Bobby', 'bob'));
echo $insert_id;
echo '<hr/>';

// output some stats about the MySQL server and client
$db->output_about();

// output the number of queries, explain statments ran against SELECTs, and total process time
$db->output_stats();

*/
/*

0.1 2010-06-17
initial version

*/
/*

References and related libs:
http://us2.php.net/manual/en/class.mysqli.php
http://search.yahoo.com/search?p=php5+mysqli+class
http://www.google.com/search?q=php+mysqli+wrapper

*/
/*

License: Revised BSD
http://en.wikipedia.org/wiki/BSD_licenses

Copyright (c) 2010-2010, Michael Brown mbrowniebytes A google.com
All rights reserved.

*/




// ok, enough blah blah, start the code


// main wrapper extending mysqli
final class phpMySQLi extends mysqli {
    private $version = '0.1';
	private $stats = array();

    protected static $instance = null;
    protected $options = array();
	protected $cache_prepared_stmt = array();


    public function __construct($opts=array()) {

		$this->stats['start'] = microtime(true);
		$this->set_options($opts);

		parent::init(); // init mysqli for options, connection

		if (!empty($this->options['mysqli_report'])) {
			$ret = mysqli_report($this->options['mysqli_report']);
		}

		if (!empty($this->options['charset'])) {
			$ret = parent::set_charset($this->options['charset']);
		}

        if (!empty($this->options['timeout'])) {
			$ret = parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->options['timeout']);
        }
		// setup database varchar/char if the data is too long to insert!
		//parent::options(MYSQLI_INIT_COMMAND, "SET sql_mode = ''");

		// connect to db
		$ret = @parent::real_connect($this->options['host'], $this->options['username'], $this->options['password'], $this->options['database'],
									 $this->options['port'], $this->options['sock'], $this->options['flags']);

        if ($ret === false || $this->connect_errno) {
            $this->db_conn_exception();
        }

    }

    public function __destruct() {
		// per-process setting; affects other scripts using mysqli; so be nice and turn off
		if (!empty($this->options['mysqli_report']) && $this->options['mysqli_report'] != MYSQLI_REPORT_OFF) {
			mysqli_report(MYSQLI_REPORT_OFF);
		}
    }

    protected function set_options(array $opts) {
        $this->options = array();
        $this->options['server']	 = 'prod'; // prod|qa|dev|etc

        $this->options['host'] 		 = 'localhost';
        $this->options['username']	 = null;
        $this->options['password']	 = null;
        $this->options['database']	 = null;

        $this->options['port']		 = 3306;
        $this->options['sock']		 = null; // socket or named pipe
        $this->options['flags']		 = null; // null ignore; MYSQLI_CLIENT_COMPRESS, etc

        $this->options['allow_admin'] 		= false; // true|false; allow admin() method
        $this->options['throw_exceptions']	= false; // true|false; type of error

        $this->options['charset']	 = null; // null ignore, else utf8, etc
        $this->options['timeout'] 	 = null; // null ignore, else nbr secs

        // null					ignore
		// MYSQLI_REPORT_OFF  	Turns reporting off
		// MYSQLI_REPORT_ERROR 	Report errors from mysqli function calls; errors
		// MYSQLI_REPORT_STRICT Report warnings from mysqli function calls; exceptions
		// MYSQLI_REPORT_INDEX 	Report if no index or bad index was used in a query
		// MYSQLI_REPORT_ALL 	Set all options (report all)
        if ($this->options['server'] == 'prod') {
			$this->options['mysqli_report'] = MYSQLI_REPORT_OFF;
		} else {
			$this->options['mysqli_report'] = MYSQLI_REPORT_ERROR;
		}

        $this->options = array_merge($this->options, $opts);
    }

    public static function get_instance($opts = array()) {
		// reuse object
        if(!self::$instance) {
            self::$instance = new self($opts);
        }
        return self::$instance;
    }

	protected function db_conn_exception() {
		if ($this->options['throw_exceptions']) {
			throw new Exception($this->connect_error, $this->connect_errno);
		} else {
			echo $this->connect_error.' ['.$this->connect_errno.']<br/>';  exit();
		}
	}

	protected function db_exception($error='') {
		$errno = $this->errno;
		if ($errno > 0) {
			$error .= ' ('.$this->error.')';
		}
		if ($this->options['throw_exceptions']) {
			throw new Exception($error, $errno);
		} else {
			echo $error.' ['.$errno.']<br/>'; exit();
		}
	}

	/*
	 * parse command out of sql; assume first word = command
	 */
	private function get_command($sql) {
		$command = strtoupper(substr($sql, 0, strpos(trim($sql), ' ')));
		//preg_match("/^\s*([a-z]+)\s+/i", $sql, $m);
		//$command = strtoupper($m[1]);
		return $command;
	}

    /*
     * mysqli::query wrapper for additional pre/post query error checking
     *
     * returns result obj|true|false; or insertid|affected rows
     */
    private function simple_query($sql, $mode=MYSQLI_STORE_RESULT) {
		$this->set_stats($sql);

		$command = $this->get_command($sql);

		if (!in_array($command, array('SELECT', 'EXPLAIN', 'SHOW', 'INSERT', 'UPDATE', 'REPLACE', 'DELETE', 'CALL', 'DROP', 'CREATE', 'GRANT', 'SET'))) {
			$this->db_exception('Unable to query sql.  Unknown command ['.$command.'].');
		}

		if (!in_array($command, array('CALL'))) {
			if (($result = $this->real_query($sql)) === false) {
				$this->db_exception('Unable to query sql.');
			}
		} else {
			if (($result = $this->multi_query($sql)) === false) {
				$this->db_exception('Unable to multi query sql.');
			}
			return $result; // multi returns true|false
		}

		// get insert/affected
		if (in_array($command, array('INSERT', 'UPDATE', 'REPLACE', 'DELETE', 'DROP', 'CREATE', 'GRANT', 'SET')) && $this->field_count == 0) {
			if ($command == 'INSERT') {
				$ret = $this->insert_id;
			} else {
				$ret = $this->affected_rows;
			}

		// result
		} else if (in_array($command, array('SELECT', 'EXPLAIN', 'SHOW')) && $this->field_count > 0) {
			if ($mode == MYSQLI_STORE_RESULT) {
				$result = $this->store_result();
				if ($result === false && $this->errno > 0) {
					$this->db_exception('Query unable to store results.');
				}
			} else {// $mode == MYSQLI_USE_RESULT
				$result = $this->use_result();
				if ($result === false && $this->errno > 0) {
					$this->db_exception('Query unable to use results.');
				}
			}
			$ret = $result;

		// errors
		} else {
			if ($result === true) {
				$this->db_exception('Query command not supported ['.$command.']');
			} else if (is_object($result)) {
				$this->db_exception('Query field count error ['.$result->field_count.']; command ['.$command.']');
			} else {
				$this->db_exception('Unknown query error.');
			}
		}

        return $ret;
    }

	private function ref_values($arr){
		if (strnatcmp(phpversion(),'5.3') >= 0) { // reference is required for PHP 5.3+
			$refs = array();
			foreach($arr as $key => $value) {
				$refs[$key] = &$arr[$key];
			}
			return $refs;
		}
		return $arr;
	}

    /*
     * prepare, optionally bind, execute
     *
     * returns result Mysqli_Result|true|false
     */
	private function prepare_bind_execute($sql, $types=null, $parms=array(), $mode=MYSQLI_STORE_RESULT) {

		$command = $this->get_command($sql);
		if (!in_array($command, array('SELECT', 'EXPLAIN', 'SHOW', 'INSERT', 'UPDATE', 'REPLACE', 'DELETE', 'CALL', 'DROP', 'CREATE', 'GRANT', 'SET'))) {
			$this->db_exception('Unable to bind sql.  Unknown command ['.$command.'].');
		}

		// cache prepared statements
		if (!isset($this->cache_prepared_stmt[$sql]) || !is_object($this->cache_prepared_stmt[$sql])) {
			if (($this->cache_prepared_stmt[$sql] = parent::prepare($sql)) === false) {
				$this->db_exception('Unable to prepare sql.');
			}
		}
		$stmt = $this->cache_prepared_stmt[$sql];

		// bind parms, if any
		if (!empty($types) || !empty($parms)) {
			if (strlen($types) != count($parms)) {
				$this->db_exception('Unable to bind parameters.  Number of types does not match number of parms.');
			}
			if (@call_user_func_array(array($stmt, 'bind_param'), array_merge(array($types), $this->ref_values($parms))) === false) {
				$this->db_exception('Unable to bind parameters.');
			}
		}

		if ($stmt->execute() === false) {
			$this->db_exception('Unable to execute query.');
		}

		if (in_array($command, array('CALL'))) {
			$ret = true;

		// buffer result
		} else if ($this->field_count > 0 && in_array($command, array('SELECT', 'EXPLAIN', 'SHOW'))) {
			if ($mode == MYSQLI_STORE_RESULT) {
				$result = $stmt->store_result();
				if ($result === false && $this->errno > 0) {
					$this->db_exception('Prepare unable to store results.');
				}

			} else { //$mode == MYSQLI_USE_RESULT
				$result = $stmt->use_result();
				if ($result === false && $this->errno > 0) {
					$this->db_exception('Prepare unable to use results.');
				}
			}
			$ret = $result;

		// get insertid
		} else if (in_array($command, array('INSERT'))) {
			$ret = $stmt->insert_id;
			$stmt->close();

		// get afffected
		} else if (in_array($command, array('UPDATE', 'REPLACE', 'DELETE', 'DROP', 'CREATE', 'GRANT', 'SET'))) {
			$ret = $stmt->affected_rows;
			$stmt->close();
		}

		return $ret;
	}



	/*
	 * stored proc sql
	 * sql str;
	 * types str;
	 *	(UN)SIGNED TINYINT: I
	 *	(UN)SIGNED SMALLINT: I
	 *	(UN)SIGNED MEDIUMINT: I
	 *	SIGNED INT: I  (large numbers > 2147483647 should use S)
	 *	UNSIGNED INT: S
	 *	(UN)SIGNED BIGINT: S
	 *	(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
	 *	FLOAT/REAL/DOUBLE (PRECISION) should all be D.
	 * parms var; array of values
	 * opts array (
	 * 		'ret_type' => MYSQLI_ASSOC|MYSQLI_NUM|MYSQLI_BOTH
	 * 		'ret_array'=> true|false
	 * returns result array or true or false
	 *
	 */
	public function proc($sql, $types=null, $parms=array(), $opts=array()) {

		$command = $this->get_command($sql);
		if (!in_array($command, array('CALL'))) {
			$this->db_exception('Only CALL allowed via db proc().');
		}

		$defaults = array(
			'ret_type' => MYSQLI_BOTH,	// MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
			'ret_array' => true 		// true return array of results; false just run
		);
		$opts = array_merge($defaults, $opts);

		if (empty($types) || empty($parms)) {
			$result = $this->simple_query($sql, $mode);

		} else {
			$result = $this->prepare_bind_execute($sql, $types, $parms, $mode);

		}

		if ($result) {
			$rows = array();
			do {
				if ($result = $this->store_result()) {
					if ($opts['ret_array']) {
						while ($row = $result->fetch_array($opts['ret_type'])) {
							$rows[] = $row;
						}
					}
					$result->close();
				}
			} while($this->next_result());
			if ($opts['ret_array']) {
				$result = $rows;
			} else {
				$result = $this->errno > 0 ? false : true;
			}
		}

		return $result;
	}

	/*
	 * select sql
	 * sql str;
	 * types str;
	 *	(UN)SIGNED TINYINT: I
	 *	(UN)SIGNED SMALLINT: I
	 *	(UN)SIGNED MEDIUMINT: I
	 *	SIGNED INT: I  (large numbers > 2147483647 should use S)
	 *	UNSIGNED INT: S
	 *	(UN)SIGNED BIGINT: S
	 *	(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
	 *	FLOAT/REAL/DOUBLE (PRECISION) should all be D.
	 * parms var; array of values
	 * mode int;
	 * 	MYSQLI_STORE_RESULT norm;
	 * 	MYSQLI_USE_RESULT very very large results; call $result->close(); before next select()
	 * returns result obj or false
	 */
	public function select($sql, $types=null, $parms=array(), $mode=MYSQLI_STORE_RESULT) {

		$command = $this->get_command($sql);
		if (!in_array($command, array('SELECT', 'EXPLAIN', 'SHOW'))) {
			$this->db_exception('Only SELECT, EXPLAIN, SHOW allowed via db select().');
		}

		if (empty($types) || empty($parms)) {
			$result = $this->simple_query($sql, $mode);

		} else {
			$result = $this->prepare_bind_execute($sql, $types, $parms, $mode);

		}

		return $result;
	}

	/*
	 * admin db sql
	 * sql str;
	 * types str;
	 *	(UN)SIGNED TINYINT: I
	 *	(UN)SIGNED SMALLINT: I
	 *	(UN)SIGNED MEDIUMINT: I
	 *	SIGNED INT: I  (large numbers > 2147483647 should use S)
	 *	UNSIGNED INT: S
	 *	(UN)SIGNED BIGINT: S
	 *	(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
	 *	FLOAT/REAL/DOUBLE (PRECISION) should all be D.
	 * parms var; array of values
	 * returns int; affected rows
	 */
	public function admin($sql, $types=null, $parms=array()) {

		if ($this->options['allow_admin'] !== true) {
			$this->db_exception('db admin() not allowed.');
		}

		$command = $this->get_command($sql);
		if (!in_array($command, array('CREATE', 'DROP', 'SET', 'GRANT'))) {
			$this->db_exception('Only CREATE, DROP, SET, GRANT allowed via db admin().');
		}

		if (empty($types) || empty($parms)) {
			$ret = $this->simple_query($sql, MYSQLI_STORE_RESULT);

		} else {
			$ret = $this->prepare_bind_execute($sql, $types, $parms);

		}

		return $ret;
	}

	/*
	 * execute crud sql
	 * sql str;
	 * types str;
	 *	(UN)SIGNED TINYINT: I
	 *	(UN)SIGNED SMALLINT: I
	 *	(UN)SIGNED MEDIUMINT: I
	 *	SIGNED INT: I  (large numbers > 2147483647 should use S)
	 *	UNSIGNED INT: S
	 *	(UN)SIGNED BIGINT: S
	 *	(VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
	 *	FLOAT/REAL/DOUBLE (PRECISION) should all be D.
	 * parms var; array of values
	 * returns int; if insert sql, last insert_id, else affected rows
	 */
	public function execute($sql, $types=null, $parms=array()) {

		$command = $this->get_command($sql);
		if (!in_array($command, array('INSERT', 'UPDATE', 'REPLACE', 'DELETE'))) {
			$this->db_exception('Only INSERT, UPDATE, REPLACE, DELETE allowed via db execute().');
		}

		if (empty($types) || empty($parms)) {
			$ret = $this->simple_query($sql, MYSQLI_STORE_RESULT);

		} else {
			$ret = $this->prepare_bind_execute($sql, $types, $parms);

		}

		return $ret;
	}

	public function insert($sql, $types=null, $parms=array()) {
		$command = $this->get_command($sql);
		if (!in_array($command, array('INSERT'))) {
			$this->db_exception('Only INSERT allowed via db insert().');
		}
		return $this->execute($sql, $types, $parms);
	}

	public function delete($sql, $types=null, $parms=array()) {
		$command = $this->get_command($sql);
		if (!in_array($command, array('DELETE'))) {
			$this->db_exception('Only DELETE allowed via db insert().');
		}
		return $this->execute($sql, $types, $parms);
	}

	public function update($sql, $types=null, $parms=array()) {
		$command = $this->get_command($sql);
		if (!in_array($command, array('UPDATE'))) {
			$this->db_exception('Only UPDATE allowed via db insert().');
		}
		return $this->execute($sql, $types, $parms);
	}

}


?>